﻿--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------store procedure---------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--ĐĂNG NHẬP--
CREATE proc sp_DangNhap
@Ten varchar(10),@MatKhau varchar(10)
as
	if  not exists (Select * from NGUOIDUNG where TenDangNhap=@Ten and MatKhau=@MatKhau)
	begin
		raiserror (N'Tên đăng nhập hoặc mật khẩu không đúng',16,1)
		return
	end
	select Quyen from NGUOIDUNG where TenDangNhap=@Ten and MatKhau=@MatKhau

GO
--Load cbViTri--
create proc sp_ViTri
as
	select * from VITRI
GO
--Load cbTrangThai
create proc sp_TrangThai
as
	select * from TRANGTHAI
GO
--Load cbPhongBan
create proc sp_PhongBan
as
	select * from PHONGBAN
GO
--Load danh sach nhan vien
create proc sp_DanhSachNhanVien
as
	select TenNhanVien,DiaChi,NgaySinh,Email,Phai,TenViTri,TenPhongBan
	from (NHANVIEN nv join PHONGBAN p on nv.MaPhongBan=p.MaPhongBan ) join VITRI vt on nv.MaViTri=vt.MaViTri
--Them NhanVien
create alter proc sp_ThemNhanVien
@tennv nvarchar(50),
@diachi nvarchar(50),
@dienthoai nvarchar(12),
@ngaysinh smalldatetime,
@email nvarchar(20),
@phai nvarchar(3),
@pass nvarchar(20),
@taikhoan nvarchar(20),
@mavitri int,
@trangthai smallint,
@phong int
as 
	declare @ma varchar(10), @i int
	set @i = 1
	set @ma = 'B5-001'
	while  exists (select * from NHANVIEN where MaNhanVien = @ma)
	Begin
		if(@i >= 99)
		Begin
			set @i = @i + 1
			set @ma = 'B5-'+convert(varchar(10),@i)
		End	
		else if(@i >= 9 and @i < 99 )
		Begin
			set @i = @i + 1
			set @ma = 'B5-0'+convert(varchar(10),@i)
		End
		else if(@i < 9)
		Begin
			set @i = @i + 1
			set @ma = 'B5-00'+convert(varchar(10),@i)
		End
	End		
	insert into NHANVIEN values(@ma,@tennv,@diachi,@dienthoai,@ngaysinh,@email,@phai,@pass,@taikhoan,@mavitri,@trangthai,@phong)
